package com.scuec.groupfive.mapper;

import com.scuec.groupfive.entity.*;
import org.apache.ibatis.annotations.*;
import java.util.Date;
import java.util.List;

@Mapper
public interface PastExamRecordMapper {

    @Select("SELECT " +
            "record_id AS recordId, " +
            "user_id AS userId, " +
            "username, " +
            "exam_type AS examType, " +
            "exam_year AS examYear, " +
            "exam_date AS examDate, " +
            "total_questions AS totalQuestions, " +
            "correct_questions AS correctQuestions, " +
            "score, " +
            "total_time AS totalTime, " +
            "created_at AS createdAt " +
            "FROM past_exam_records " +
            "WHERE user_id = #{userId} " +
            "ORDER BY exam_date DESC " +
            "LIMIT #{offset}, #{size}")
    List<PastExam> getPastExamRecordsByUserId(
            @Param("userId") Long userId,
            @Param("offset") int offset,
            @Param("size") int size
    );

    @Select("<script>" +
            "SELECT " +
            "record_id AS recordId, " +
            "user_id AS userId, " +
            "username, " +
            "exam_type AS examType, " +
            "exam_year AS examYear, " +
            "exam_date AS examDate, " +
            "total_questions AS totalQuestions, " +
            "correct_questions AS correctQuestions, " +
            "score, " +
            "total_time AS totalTime, " +
            "created_at AS createdAt " +
            "FROM past_exam_records " +
            "WHERE user_id = #{userId} " +
            "<if test='startDate != null and endDate != null'>" +
            "AND exam_date BETWEEN #{startDate} AND #{endDate} " +
            "</if>" +
            "<if test='keyword != null and keyword != \"\"'>" +
            "AND (exam_type LIKE CONCAT('%', #{keyword}, '%') " +
            "OR CAST(exam_year AS CHAR) LIKE CONCAT('%', #{keyword}, '%')) " +
            "</if>" +
            "ORDER BY exam_date DESC " +
            "LIMIT #{offset}, #{size}" +
            "</script>")
    List<PastExam> searchPastExamRecords(
            @Param("userId") Long userId,
            @Param("startDate") Date startDate,
            @Param("endDate") Date endDate,
            @Param("keyword") String keyword,
            @Param("offset") int offset,
            @Param("size") int size
    );

    @Select("SELECT * FROM past_exam_questions WHERE record_id = #{recordId} ORDER BY sequence")
    List<PastExamQuestion> getPastExamQuestionsByRecordId(@Param("recordId") Long recordId);

    @Select("SELECT " +
            "record_id AS recordId, " +
            "user_id AS userId, " +
            "username, " +
            "exam_type AS examType, " +
            "exam_year AS examYear, " +
            "exam_date AS examDate, " +
            "total_questions AS totalQuestions, " +
            "correct_questions AS correctQuestions, " +
            "score, " +
            "total_time AS totalTime, " +
            "created_at AS createdAt " +
            "FROM past_exam_records WHERE record_id = #{recordId}")
    PastExam getPastExamRecordById(@Param("recordId") Long recordId);

    @Select("SELECT q.* FROM questions q " +
            "JOIN past_exam_questions peq ON q.question_id = peq.question_id " +
            "WHERE peq.record_id = #{recordId} " +
            "ORDER BY peq.sequence")
    List<QuestionInfo> getQuestionsByRecordId(@Param("recordId") Long recordId);

    @Select("SELECT cq.* FROM choice_questions cq " +
            "JOIN past_exam_questions peq ON cq.question_id = peq.question_id " +
            "WHERE peq.record_id = #{recordId} AND peq.question_type LIKE 'single_choice%'")
    List<ChoiceQuestion> getChoiceQuestionsByRecordId(@Param("recordId") Long recordId);

    @Select("SELECT fq.* FROM fill_blank_questions fq " +
            "JOIN past_exam_questions peq ON fq.question_id = peq.question_id " +
            "WHERE peq.record_id = #{recordId} AND peq.question_type = 'fill_in_the_blank'")
    List<FillBlankQuestion> getFillBlankQuestionsByRecordId(@Param("recordId") Long recordId);

    @Select("SELECT sq.* FROM subjective_questions sq " +
            "JOIN past_exam_questions peq ON sq.question_id = peq.question_id " +
            "WHERE peq.record_id = #{recordId} AND peq.question_type = 'subjective'")
    List<SubjectiveQuestion> getSubjectiveQuestionsByRecordId(@Param("recordId") Long recordId);


    @Select("SELECT " +
            "CASE " +
            "WHEN q.question_type LIKE 'single_choice%' THEN cq.answer " +
            "WHEN q.question_type = 'fill_in_the_blank' THEN fq.blanks " +
            "WHEN q.question_type = 'subjective' THEN sq.reference_answer " +
            "ELSE '' END AS correct_answer " +
            "FROM questions q " +
            "LEFT JOIN choice_questions cq ON q.question_id = cq.question_id " +
            "LEFT JOIN fill_blank_questions fq ON q.question_id = fq.question_id " +
            "LEFT JOIN subjective_questions sq ON q.question_id = sq.question_id " +
            "WHERE q.question_id = #{questionId}")
    String getCorrectAnswer(Long questionId);

    // 插入考试记录
    @Insert("INSERT INTO past_exam_records(user_id, username, exam_type, exam_year, " +
            "exam_date, total_questions, correct_questions, score, total_time) " +
            "VALUES(#{userId}, #{username}, #{examType}, #{examYear}, #{examDate}, " +
            "#{totalQuestions}, #{correctQuestions}, #{score}, #{totalTime})")
    @Options(useGeneratedKeys = true, keyProperty = "recordId")
    int insertPastExamRecord(PastExam record);

    // 插入考试题目记录
    @Insert("INSERT INTO past_exam_questions(record_id, question_id, question_type, " +
            "user_answer, is_correct, sequence) " +
            "VALUES(#{recordId}, #{questionId}, #{questionType}, " +
            "#{userAnswer}, #{isCorrect}, #{sequence})")
    int insertPastExamQuestion(PastExamQuestion question);

    // 获取选择题答案
    @Select("SELECT answer FROM choice_questions WHERE question_id = #{questionId}")
    String getChoiceAnswer(Long questionId);

    // 获取填空题答案
    @Select("SELECT answer FROM fill_blank_questions WHERE question_id = #{questionId}")
    String getFillBlankAnswer(Long questionId);

    // 获取用户名
    @Select("SELECT username FROM users WHERE user_id = #{userId}")
    String getUsernameById(Long userId);
    // PastExamRecordMapper.java
// 添加以下方法：
    @Select("SELECT status FROM favorites WHERE user_id = #{userId} AND question_id = #{questionId}")
    Integer getFavoriteStatus(@Param("userId") Long userId, @Param("questionId") Long questionId);
}